-- @author shaodongyang
-- ods主站详情日志

use hdp_lbg_supin_zplisting;

insert overwrite table ods_zp_n_n_infodetail partition (dt='${dateSuffix}')
select
    t1.url,
    t1.refer refer_url,
    t1.spm,
    t1.utm_source,
    t1.sid,
    t1.pid,
    t1.displocal displocalpath,
    t1.dispcate dispcatepath,
    '-' clientlocalpath,
    t1.uid userid,
    t1.id58 cookieid,
    t1.imei,
    '-' guestuuid,
    t1.ip,
    t1.xforward,
    t1.useragent,
    t1.os,
    t1.version app_version,
    t1.logtime stime,
    t1.platform,
    t1.pagetype page_type,
    t1.lon longitude,
    t1.lat latitude,
    t1.source,
    t1.scene,
    t1.tjfrom,
    t1.infoid info_id,
    t1.userid info_buid,
    get_json_object(t1.infomsg, '$.parentuserid') info_parentuserid,
    get_json_object(t1.infomsg, '$.auth') info_authentication,
    get_json_object(t1.infomsg, '$.wads') info_work_address,
    get_json_object(t1.infomsg, '$.fuli') info_welfare,
    get_json_object(t1.infomsg, '$.title') info_title,
    get_json_object(t1.infomsg, '$.state') info_state,
    get_json_object(t1.infomsg, '$.salary') info_salary,
    get_json_object(t1.infomsg, '$.rel') info_relation,
    get_json_object(t1.infomsg, '$.jobclosed') info_jobclosed,
    get_json_object(t1.infomsg, '$.num') info_headcount,
    get_json_object(t1.infomsg, '$.nx') info_experience,
    get_json_object(t1.infomsg, '$.entsizeid') info_enterprise_size_id,
    get_json_object(t1.infomsg, '$.entsize') info_enterprise_size,
    get_json_object(t1.infomsg, '$.natureid') info_enterprise_nature_id,
    get_json_object(t1.infomsg, '$.nature') info_enterprise_nature,
    get_json_object(t1.infomsg, '$.entname') info_enterprise_name,
    get_json_object(t1.infomsg, '$.entcateid') info_enterprise_industry_id,
    get_json_object(t1.infomsg, '$.entcate') info_enterprise_industry,
    get_json_object(t1.infomsg, '$.xueli') info_education,
    get_json_object(t1.infomsg, '$.deliver') info_delivery_cnt,
    get_json_object(t1.infomsg, '$.browse') info_click_cnt,
    get_json_object(t1.infomsg, '$.huiy') info_buser_vip,
    get_json_object(t1.infomsg, '$.online') info_buser_online,
    get_json_object(t1.infomsg, '$.position') info_buser_job,
    get_json_object(t1.infomsg, '$.live') info_buser_activity,
    get_json_object(t1.infomsg, '$.avg') info_avg_salary,
    get_json_object(t1.infomsg, '$.infoadtypes') info_types,
    t1.zpsrc
from
    (
        select
            *
        from
            hdp_lbg_zhaopin_defaultdb.zzdetail
        where
            dt = '${dateSuffix}'
    ) t1
    left join dim_zp_wb_n_stress_testing_ip t2
    on t1.ip = t2.ip
where
    t2.ip is null;
